const sqlite3 = require('sqlite3').verbose();

// 初始化数据库
const db = new sqlite3.Database('./excel_system.db', (err) => {
    if (err) {
        console.error('数据库连接失败:', err);
    } else {
        console.log('数据库连接成功');
        // 设置数据库编码为UTF-8
        db.run("PRAGMA encoding = 'UTF-8'");
        db.run("PRAGMA journal_mode = WAL");
        db.run("PRAGMA synchronous = NORMAL");
        // 确保文本排序规则支持UTF-8
        db.run("PRAGMA case_sensitive_like = true");
    }
});

// 创建表
const initializeTables = () => {
    db.serialize(() => {
        // 文件主表 - 管理不同的文件
        db.run(`CREATE TABLE IF NOT EXISTS files (
            id TEXT PRIMARY KEY,
            original_name TEXT NOT NULL,
            file_hash TEXT,
            created_by TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(original_name)
        )`);

        // 文件版本表 - 每个文件的不同版本
        db.run(`CREATE TABLE IF NOT EXISTS file_versions (
            id TEXT PRIMARY KEY,
            file_id TEXT NOT NULL,
            version INTEGER NOT NULL,
            filename TEXT NOT NULL,
            file_path TEXT NOT NULL,
            submitter TEXT NOT NULL,
            change_description TEXT,
            file_size INTEGER,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (file_id) REFERENCES files (id),
            UNIQUE(file_id, version)
        )`);

        // 表格数据表
        db.run(`CREATE TABLE IF NOT EXISTS table_data (
            id TEXT PRIMARY KEY,
            version_id TEXT NOT NULL,
            row_index INTEGER NOT NULL,
            column_name TEXT NOT NULL,
            cell_value TEXT,
            cell_type TEXT DEFAULT 'text',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (version_id) REFERENCES file_versions (id)
        )`);

        // 批注表
        db.run(`CREATE TABLE IF NOT EXISTS annotations (
            id TEXT PRIMARY KEY,
            version_id TEXT NOT NULL,
            row_index INTEGER NOT NULL,
            column_name TEXT NOT NULL,
            annotation_type TEXT NOT NULL,
            annotation_data TEXT,
            created_by TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (version_id) REFERENCES file_versions (id)
        )`);

        // 自定义列表
        db.run(`CREATE TABLE IF NOT EXISTS custom_columns (
            id TEXT PRIMARY KEY,
            version_id TEXT NOT NULL,
            column_name TEXT NOT NULL,
            column_type TEXT DEFAULT 'text',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (version_id) REFERENCES file_versions (id)
        )`);
    });
};

module.exports = { db, initializeTables };